package cn.edu.lingnan.dao;

import cn.edu.lingnan.pojo.Student;
import cn.edu.lingnan.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;

public class StudentDaoMysqlImpl implements StudentDao{
    @Override
    public Student findStudentByNameAndPassword(String _sname, String _spassword) {
        Student student = new Student();
        Connection conn=null;
        Statement stat=null;
        ResultSet rs=null;

        try {
            conn=DBUtil.getInstance().getConnection();
           stat= conn.createStatement();
           rs= stat.executeQuery
                    ("select * from student where sname ='"+_sname+"' and spassword='"+_spassword+"' and sflag=1");
            if (rs.next()){
                System.out.println(rs.getString("sid"));
                student.setSid(rs.getString("sid"));
                student.setSname(rs.getString("sname"));
                student.setSpassword(rs.getString("spassword"));
                student.setSright(rs.getInt("sright"));
                student.setSflag(rs.getInt("sflag"));
            }

        }  catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.getInstance().close(rs,stat,conn);
        }

        return student;
    }

    @Override
    public List<Student> findStudentAll() {
        List<Student> list=new ArrayList<>();
        Connection conn=null;
        Statement stat=null;
        ResultSet rs=null;

        try {
            conn=DBUtil.getInstance().getConnection();
            stat= conn.createStatement();
            rs= stat.executeQuery
                    ("select * from student");
            while (rs.next()){
                Student student=new  Student();
                student.setSid(rs.getString("sid"));
                student.setSname(rs.getString("sname"));
                student.setSpassword(rs.getString("spassword"));
                student.setSright(rs.getInt("sright"));
                student.setSflag(rs.getInt("sflag"));
                list.add(student);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.getInstance().close(rs,stat,conn);
        }


        return list;
    }

    @Override
    public Student findStudentBySid(String _sid) {
        Student student=new Student();
        Connection conn=null;
        Statement stat=null;
        ResultSet rs=null;

        try {
            conn=DBUtil.getInstance().getConnection();
            stat= conn.createStatement();
            rs= stat.executeQuery
                    ("select * from student where sid='"+_sid+"'");
            if (rs.next()){
                student.setSid(rs.getString("sid"));
                student.setSname(rs.getString("sname"));
                student.setSpassword(rs.getString("spassword"));
                student.setSright(rs.getInt("sright"));
                student.setSflag(rs.getInt("sflag"));
            }

        }  catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.getInstance().close(rs,stat,conn);
        }
        return student;
    }

    @Override
    public List<Student> findStudentBySname(String _sname) {
        List<Student> list=new ArrayList<>();
        Connection conn=null;
        PreparedStatement prep=null;

        ResultSet rs=null;

        try {
            conn=DBUtil.getInstance().getConnection();
            prep=conn.prepareStatement("select * from student where sname like ?");
            prep.setString(1,"%"+_sname+"%");
            rs=prep.executeQuery();
            while (rs.next()){
                Student student=new  Student();
                student.setSid(rs.getString("sid"));
                student.setSname(rs.getString("sname"));
                student.setSpassword(rs.getString("spassword"));
                student.setSright(rs.getInt("sright"));
                student.setSflag(rs.getInt("sflag"));
                list.add(student);
            }

        }  catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.getInstance().close(rs,prep,conn);
        }
        return list;
    }

    @Override
    public boolean deleteStudentBySid(String _sid) {
        boolean flag = false;
        Connection conn = null;
        PreparedStatement prep = null;

        try {
            conn = DBUtil.getInstance().getConnection();
            conn.setAutoCommit(false); // Disable auto-commit

            prep = conn.prepareStatement("DELETE FROM student WHERE sid = ?");
            prep.setString(1, _sid);
            int rowsAffected = prep.executeUpdate();

            if (rowsAffected > 0) {
                flag = true;
            }

            conn.commit(); // Commit the transaction

        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback(); // Rollback the transaction on error
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true); // Restore auto-commit mode
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            DBUtil.getInstance().close(prep, conn); // Close the connection
        }

        return flag;
    }
    public boolean addStudent(Student student) {
        boolean flag = false;
        Connection conn = null;
        PreparedStatement prep = null;

        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("INSERT INTO student (sid, sname, spassword, sright, sflag) VALUES (?, ?, ?, ?, ?)");
            prep.setString(1, student.getSid());
            prep.setString(2, student.getSname());
            // 设置默认密码为 "1234"
            prep.setString(3, "1234");
            // 权限默认设置为0
            prep.setInt(4, 0);
            // 标志默认设置为1
            prep.setInt(5, 1);

            int result = prep.executeUpdate();
            if (result > 0) {
                flag = true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.getInstance().close(prep, conn);
        }

        return flag;
    }
}



